﻿using DBUtil;
using DBUtil.Provider.SqlServer;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using DotNetCommon.Extensions;
using NUnit.Framework;
using Shouldly;
using System.Threading.Tasks;
using System.Data.Common;

namespace Test.SqlServer.Curd.Select
{
    [TestFixture]
    public sealed class SelectTestsAsync : TestBase
    {
        #region 测试SelectScalar
        [Test]
        public async Task SelectScalarTest()
        {
            //准备数据
            if (db.IsTableExist("test")) db.Manage.DropTable("test");
            db.ExecuteSql("create table test(id int primary key,name varchar(50),birth datetime)");
            var now = DateTime.Now;
            var res = db.Insert("test",
                new { id = 1, name = "小明", birth = now }.ToDictionary(),
                new { id = 2, name = "小花", birth = now }.ToDictionary(),
                new { id = 3, name = "小刚" }.ToDictionary());
            Assert.IsTrue(res == 3);
            //测试返回int
            res = await db.SelectScalarAsync<int>("select count(1) from test");
            Assert.IsTrue(res == 3);
            //测试返回int? 实际为null
            var res2 = await db.SelectScalarAsync<int?>("select id from test where name='不存在'");
            Assert.IsNull(res2);

            //测试返回int? 实际为2
            res2 = await db.SelectScalarAsync<int?>("select id from test where name='小花'");
            Assert.IsNotNull(res2);
            Assert.IsTrue(res2.Value == 2);

            //测试返回字符串
            var name = await db.SelectScalarAsync<string>($"select name from test where id ={db.ParaPrefix}p_id", new { p_id = 1 }.ToDictionary());
            Assert.IsTrue(name == "小明");

            //测试返回DateTime
            var birth = await db.SelectScalarAsync<DateTime>("select birth from test where id=1");
            Assert.IsTrue(birth.ToString("yyyy-MM-dd") == now.ToString("yyyy-MM-dd"));

            //测试返回DateTime? 实际为null
            var birth2 = await db.SelectScalarAsync<DateTime?>("select birth from test where id=3");
            Assert.IsNull(birth2);
            //测试返回DateTime? 实际有值
            birth2 = await db.SelectScalarAsync<DateTime?>("select birth from test where id=2");
            Assert.IsNotNull(birth2);

            //测试返回枚举
            var res3 = await db.SelectScalarAsync<TestEnum>("select id from test where id=1");
            Assert.IsTrue(res3 == TestEnum.Open);

            res3 = await db.SelectScalarAsync<TestEnum>("select 0");
            Assert.IsTrue(res3 == TestEnum.None);

            var res4 = await db.SelectScalarAsync<TestEnum?>("select 4");
            Assert.IsNotNull(res4);
            Assert.IsTrue((int)res4.Value == 4);

            res4 = await db.SelectScalarAsync<TestEnum?>("select 2");
            Assert.IsNotNull(res4);
            Assert.IsTrue(res4.Value == TestEnum.Close);

            //测试返回bool
            var res5 = await db.SelectScalarAsync<bool>("select 1");
            Assert.IsTrue(res5);
            res5 = await db.SelectScalarAsync<bool>("select 0");
            Assert.IsFalse(res5);
            res5 = await db.SelectScalarAsync<bool>("select -1");
            Assert.IsTrue(res5);
            res5 = await db.SelectScalarAsync<bool>("select 1");
            Assert.IsTrue(res5);

            res5 = await db.SelectScalarAsync<bool>("select 'true'");
            Assert.IsTrue(res5);
            res5 = await db.SelectScalarAsync<bool>("select 'false'");
            Assert.IsFalse(res5);
            res5 = await db.SelectScalarAsync<bool>("select 'True'");
            Assert.IsTrue(res5);
            res5 = await db.SelectScalarAsync<bool>("select 'False'");
            Assert.IsFalse(res5);

            //测试返回可空bool
            var res6 = await db.SelectScalarAsync<bool?>("select 1");
            Assert.IsNotNull(res6);
            Assert.IsTrue(res6.Value);
            res6 = await db.SelectScalarAsync<bool?>("select 0");
            Assert.IsNotNull(res6);
            Assert.IsFalse(res6.Value);

            res6 = await db.SelectScalarAsync<bool?>("select null");
            Assert.IsNull(res6);

            res6 = await db.SelectScalarAsync<bool?>("select id from test where id =-1");
            Assert.IsNull(res6);
        }

        [Test]
        public async Task SelectScalarTest2()
        {
            //准备数据
            if (db.IsTableExist("test")) db.Manage.DropTable("test");
            db.ExecuteSql("create table test(id int primary key,name varchar(50),birth datetime)");
            var now = DateTime.Now;
            var res = db.Insert("test",
                new { id = 1, name = "小明", birth = now }.ToDictionary(),
                new { id = 2, name = "小花", birth = now }.ToDictionary(),
                new { id = 3, name = "小刚" }.ToDictionary());
            Assert.IsTrue(res == 3);

            var res2 = await db.SelectScalarAsync<int?>("select id from test where name =@name", new { name = "小花" }.ToDictionary());
            Assert.IsNotNull(res2);
            Assert.IsTrue(res2.Value == 2);

            res2 = await db.SelectScalarAsync<int?>("select id from test where name =@name", new { name = "小明" }.ToDictionary());
            Assert.IsNotNull(res2);
            Assert.IsTrue(res2.Value == 1);

            res2 = await db.SelectScalarAsync<int?>("select id from test where name ='小刚'");
            Assert.IsNotNull(res2);
            Assert.IsTrue(res2.Value == 3);

            //测试参数
            db.Manage.DropProcedureIfExist("usp_test");
            db.ExecuteSql(@"create proc usp_test
@userid int =1,
@pwd varchar(50) output
as
set nocount on;
begin
	declare @age int=18
    select @userid as id,@age as age,'小明' as name
	set @pwd=CONVERT(varchar,@userid)+'_'+'123456'
end");

            var pwd = db.CreatePara("pwd", null, System.Data.ParameterDirection.Output, size: 50);
            var userid = db.CreatePara("userid", 1);
            var result = await db.SelectScalarAsync<int>("usp_test", System.Data.CommandType.StoredProcedure, 30, new DbParameter[] { userid, pwd });
            result.ShouldBe(1);
            pwd.Value.ShouldBe("1_123456");
        }
        #endregion

        #region 测试SelectDataTable
        [Test]
        public async Task SelectDataTableTest()
        {
            //准备数据
            if (db.IsTableExist("test")) db.Manage.DropTable("test");
            db.ExecuteSql("create table test(id int primary key,name varchar(50),birth datetime)");
            var now = DateTime.Now;
            var res = db.Insert("test",
                new { id = 1, name = "小明", birth = now }.ToDictionary(),
                new { id = 2, name = "小花", birth = now }.ToDictionary(),
                new { id = 3, name = "小刚", birth = now }.ToDictionary());
            Assert.IsTrue(res == 3);
            var dt = await db.SelectDataTableAsync("select * from test where id>1");
            Assert.IsNotNull(dt);
            Assert.IsTrue(dt.Rows.Count == 2);
            Assert.IsTrue(dt.Rows[1]["id"].ToString() == "3");
            Assert.IsTrue(dt.Rows[1]["name"].ToString() == "小刚");

            dt = await db.SelectDataTableAsync("select * from test where id>@id", new { id = 1 }.ToDictionary());
            Assert.IsNotNull(dt);
            Assert.IsTrue(dt.Rows.Count == 2);
            Assert.IsTrue(dt.Rows[1]["id"].ToString() == "3");
            Assert.IsTrue(dt.Rows[1]["name"].ToString() == "小刚");

            dt = await db.SelectDataTableAsync("select * from test where id>@id", new { id = 1 }.ToDictionary());
            Assert.IsNotNull(dt);
            Assert.IsTrue(dt.Rows.Count == 2);
            Assert.IsTrue(dt.Rows[1]["id"].ToString() == "3");
            Assert.IsTrue(dt.Rows[1]["name"].ToString() == "小刚");
        }
        #endregion

        #region 测试SelectDataSet
        [Test]
        public async Task SelectDataSetTest()
        {
            //准备数据
            if (db.IsTableExist("test")) db.Manage.DropTable("test");
            db.ExecuteSql("create table test(id int primary key,name varchar(50),birth datetime)");
            var now = DateTime.Now;
            var res = db.Insert("test",
                new { id = 1, name = "小明", birth = now }.ToDictionary(),
                new { id = 2, name = "小花", birth = now }.ToDictionary(),
                new { id = 3, name = "小刚", birth = now }.ToDictionary());
            Assert.IsTrue(res == 3);
            var ds = await db.SelectDataSetAsync("select * from test where id>1;select * from test where id=1;");
            Assert.IsNotNull(ds);
            Assert.IsTrue(ds.Tables.Count == 2);
            Assert.IsTrue(ds.Tables[0].Rows.Count == 2);
            Assert.IsTrue(ds.Tables[1].Rows.Count == 1);
            Assert.IsTrue(ds.Tables[0].Rows[1]["id"].ToString() == "3");
            Assert.IsTrue(ds.Tables[1].Rows[0]["name"].ToString() == "小明");

            ds = await db.SelectDataSetAsync("select * from test where id>@id;select * from test where id=@id2;", new { id = 1, id2 = 1 }.ToDictionary());
            Assert.IsNotNull(ds);
            Assert.IsTrue(ds.Tables.Count == 2);
            Assert.IsTrue(ds.Tables[0].Rows.Count == 2);
            Assert.IsTrue(ds.Tables[1].Rows.Count == 1);
            Assert.IsTrue(ds.Tables[0].Rows[1]["id"].ToString() == "3");
            Assert.IsTrue(ds.Tables[1].Rows[0]["name"].ToString() == "小明");

            ds = await db.SelectDataSetAsync("select * from test where id>@id;select * from test where id=@id;", new { id = 1 }.ToDictionary());
            Assert.IsNotNull(ds);
            Assert.IsTrue(ds.Tables.Count == 2);
            Assert.IsTrue(ds.Tables[0].Rows.Count == 2);
            Assert.IsTrue(ds.Tables[1].Rows.Count == 1);
            Assert.IsTrue(ds.Tables[0].Rows[1]["id"].ToString() == "3");
            Assert.IsTrue(ds.Tables[1].Rows[0]["name"].ToString() == "小明");
        }
        #endregion

        #region 测试SelectDataReader
        [Test]
        public async Task SelectDataReaderTest()
        {
            //准备数据
            if (db.IsTableExist("test")) db.Manage.DropTable("test");
            db.ExecuteSql("create table test(id int primary key,name varchar(50),birth datetime)");
            var now = DateTime.Now;
            var res = db.Insert("test",
                new { id = 1, name = "小明", birth = now }.ToDictionary(),
                new { id = 2, name = "小花", birth = now }.ToDictionary(),
                new { id = 3, name = "小刚" }.ToDictionary());
            Assert.IsTrue(res == 3);
            await db.SelectDataReaderAsync(async reader =>
            {
                Assert.IsTrue(reader.FieldCount == 3);
                var index = 0;
                while (await reader.ReadAsync())
                {
                    var id = reader.GetInt32(0);
                    var name = reader.GetString(1);
                    var birth = reader["birth"];
                    if (index == 0)
                    {
                        Assert.IsTrue(id == 1);
                        Assert.IsTrue(name == "小明");
                    }
                    else if (index == 1)
                    {
                        Assert.IsTrue(id == 2);
                        Assert.IsTrue(name == "小花");
                    }
                    else if (index == 2)
                    {
                        Assert.IsTrue(id == 3);
                        Assert.IsTrue(name == "小刚");
                        Assert.IsTrue(birth is DBNull || birth is null);
                    }
                    index++;
                }
            }, "select * from test");

            await db.SelectDataReaderAsync(async reader =>
           {
               Assert.IsTrue(reader.FieldCount == 3);
           }, "select * from test where id>@id", new { id = 1 }.ToDictionary());

            await db.SelectDataReaderAsync(async reader =>
            {
                Assert.IsTrue(reader.FieldCount == 3);
            }, "select * from test where id>@id", new { id = 1 }.ToDictionary());
        }
        #endregion

        #region 测试SelectDicionary

        [Test]
        public async Task SelectDicionaryTest()
        {
            //准备数据
            if (db.IsTableExist("test")) db.Manage.DropTable("test");
            db.ExecuteSql("create table test(id int primary key,name varchar(50),birth datetime)");
            var now = DateTime.Now;
            var res = db.Insert("test",
                new { id = 1, name = "小明", birth = now }.ToDictionary(),
                new { id = 2, name = "小花", birth = now }.ToDictionary(),
                new { id = 3, name = "小刚" }.ToDictionary());
            Assert.IsTrue(res == 3);

            //多行数据
            var dics = await db.SelectDictionaryListAsync("select * from test");
            Assert.IsNotNull(dics);
            Assert.IsTrue(dics.Count == 3);
            var row = dics[0];
            Assert.IsTrue(row["id"].ToString() == "1");
            Assert.IsTrue(row["name"].ToString() == "小明");
            Assert.IsTrue(row["birth"] is DateTime);

            row = dics[2];
            Assert.IsTrue(row["id"].ToString() == "3");
            Assert.IsTrue(row["name"].ToString() == "小刚");
            Assert.IsTrue(row["birth"] is null || row["birth"] is DBNull);

            dics = await db.SelectDictionaryListAsync("select * from test where id>@id", new { id = 1 }.ToDictionary());
            Assert.IsNotNull(dics);
            Assert.IsTrue(dics.Count == 2);

            dics = await db.SelectDictionaryListAsync("select * from test where id=@id", new { id = 1 }.ToDictionary());
            Assert.IsNotNull(dics);
            Assert.IsTrue(dics.Count == 1);

            //单行数据
            var dic = await db.SelectDictionaryAsync("select * from test where id=1");
            Assert.IsNotNull(dic);
            Assert.IsTrue(dic.Count == 3);
            Assert.IsTrue(dic["id"].ToString() == "1");
            Assert.IsTrue(dic["name"].ToString() == "小明");
            Assert.IsTrue(dic["birth"] is DateTime);

            dic = await db.SelectDictionaryAsync("select * from test where id=@id", new { id = 1 }.ToDictionary());
            Assert.IsNotNull(dic);
            Assert.IsTrue(dic.Count == 3);
            Assert.IsTrue(dic["id"].ToString() == "1");
            Assert.IsTrue(dic["name"].ToString() == "小明");
            Assert.IsTrue(dic["birth"] is DateTime);

            dic = await db.SelectDictionaryAsync("select * from test where id=@id", new { id = 1 }.ToDictionary());
            Assert.IsNotNull(dic);
            Assert.IsTrue(dic.Count == 3);
            Assert.IsTrue(dic["id"].ToString() == "1");
            Assert.IsTrue(dic["name"].ToString() == "小明");
            Assert.IsTrue(dic["birth"] is DateTime);

        }
        #endregion

        #region 测试SelectModel
        [Test]
        public async Task SelectModelTest()
        {
            //准备数据
            if (db.IsTableExist("test")) db.Manage.DropTable("test");
            db.ExecuteSql("create table test(id int primary key,name varchar(50),birth datetime)");
            var now = DateTime.Now;
            var res = db.Insert("test",
                new { id = 1, name = "小明", birth = DateTime.Now }.ToDictionary(),
                new { id = 2, name = "小花", birth = DateTime.Now.AddYears(-20) }.ToDictionary(),
                new { id = 3, name = "小刚", birth = DateTime.Now }.ToDictionary());
            Assert.IsTrue(res == 3);
            //测试SelectModels
            var models = await db.SelectModelListAsync<Person>("select * from test");
            Assert.IsTrue(models.Count == 3);
            Assert.IsTrue(models.Last().Id == 3);

            models = await db.SelectModelListAsync<Person>("select * from test where id=@id", new { id = 1 }.ToDictionary());
            Assert.IsTrue(models.Count == 1);
            Assert.IsTrue(models.Last().Id == 1);

            models = await db.SelectModelListAsync<Person>("select * from test where id=@id", new { id = 2 }.ToDictionary());
            Assert.IsTrue(models.Count == 1);
            Assert.IsTrue(models.Last().Id == 2);


            //测试SelectModel
            var model = await db.SelectModelAsync<Person>("select * from test");
            Assert.IsNotNull(model);
            Assert.IsTrue(model.Id == 1);

            model = await db.SelectModelAsync<Person>("select * from test where id=@id", new { id = 1 }.ToDictionary());
            Assert.IsNotNull(model);
            Assert.IsTrue(model.Id == 1);

            model = await db.SelectModelAsync<Person>("select * from test where id=@id", new { id = 3 }.ToDictionary());
            Assert.IsNotNull(model);
            Assert.IsTrue(model.Id == 3);
        }
        #endregion

        #region 测试参数化查询 in & like & between...and
        /// <summary>
        /// 参数化in
        /// </summary>
        [Test]
        public async Task ParameterIn()
        {
            //准备数据
            if (db.IsTableExist("test")) db.Manage.DropTable("test");
            db.ExecuteSql("create table test(id int primary key,name varchar(50),birth datetime)");
            var now = DateTime.Now;
            var res = db.Insert("test",
                new { id = 1, name = "小明", birth = DateTime.Now }.ToDictionary(),
                new { id = 2, name = "小花", birth = DateTime.Now.AddYears(-20) }.ToDictionary(),
                new { id = 3, name = "小刚" }.ToDictionary());
            Assert.IsTrue(res == 3);
            //参数化in
            var persons = await db.SelectModelListAsync<Person>("select * from test where id in (@id1,@id2)", new { id1 = 1, id2 = 2 }.ToDictionary());
            Assert.IsTrue(persons.Count == 2);
        }

        /// <summary>
        /// 参数化like
        /// </summary>
        [Test]
        public async Task ParameterLike()
        {
            //准备数据
            if (db.IsTableExist("test")) db.Manage.DropTable("test");
            db.ExecuteSql("create table test(id int primary key,name varchar(50),birth datetime)");
            var now = DateTime.Now;
            var res = db.Insert("test",
                new { id = 1, name = "小明", birth = DateTime.Now }.ToDictionary(),
                new { id = 2, name = "小花", birth = DateTime.Now.AddYears(-20) }.ToDictionary(),
                new { id = 3, name = "小刚" }.ToDictionary());
            Assert.IsTrue(res == 3);
            //参数化in
            var persons = await db.SelectModelListAsync<Person>("select * from test where name like @name", new { name = "%花%" }.ToDictionary());
            Assert.IsTrue(persons.Count == 1);
        }

        /// <summary>
        /// 参数化Between...and
        /// </summary>
        [Test]
        public async Task ParameterBetween()
        {
            //准备数据
            if (db.IsTableExist("test")) db.Manage.DropTable("test");
            db.ExecuteSql("create table test(id int primary key,name varchar(50),birth datetime)");
            var now = DateTime.Now;
            var res = db.Insert("test",
                new { id = 1, name = "小明", birth = DateTime.Now }.ToDictionary(),
                new { id = 2, name = "小花", birth = DateTime.Now.AddYears(-20) }.ToDictionary(),
                new { id = 3, name = "小刚" }.ToDictionary());
            Assert.IsTrue(res == 3);
            //参数化in
            var persons = await db.SelectModelListAsync<Person>("select * from test where birth between @start and @end", new { start = DateTime.Now.AddDays(-1), end = DateTime.Now }.ToDictionary());
            Assert.IsTrue(persons.Count == 1);
            Assert.IsTrue(persons.FirstOrDefault().Id == 1);
        }
        #endregion
    }
}
